<?php

namespace app\services\data\stat\ad\normal;

use app\clickhouse\models\Events;

class StatKeep extends StatBaseUserEvent
{
    protected $keepType; // 留存类型
    protected $keepDays; // 统计留存天
    protected $maxCutNum = 50; // 每个sql最多统计多少天激活的留存
    protected $firstEventId; // 事件1
    protected $secondEventId; // 留存事件2
    protected $money; // 首冲金额

    public function __construct($params)
    {
        parent::__construct($params);

        $today = strtotime('today');
        if ($this->etime > $today) {
            $this->etime = $today;
        }
        if ($this->etime > $this->stime) {
            $this->etime -= 86400;
        }

        $keep_days = $params['keep_days'] ?? [2, 3, 5];
        $this->keepDays = $keep_days;

        $this->keepType = $params['keep_type'] ?? 1;
        switch ($this->keepType) {
            case 1:
            case 3:
            case 5:
                $this->firstEventId = Events::EVENT_REGISTER;
                $this->secondEventId = Events::EVENT_LOGIN;
                break;
            case 2:
            case 6:
                $this->firstEventId = Events::EVENT_PAY;
                $this->secondEventId = Events::EVENT_LOGIN;
                break;
            case 4:
            case 7:
                $this->firstEventId = Events::EVENT_LOGIN;
                $this->secondEventId = Events::EVENT_LOGIN;
            case 8:
                $this->firstEventId = Events::EVENT_ROLE_REGISTER;
                $this->secondEventId = Events::EVENT_ROLE_LOGIN;
                break;
        }

        $this->money = $params['pay_one_money'] ?? 6;
    }

    public function getKeepDays()
    {
        return $this->keepDays;
    }

    // 当天注册并付费
    protected function getRetentionFirstSql($reg_day)
    {
        // 注册留存、付费留存、活跃用户留存
        if (in_array($this->keepType, [1, 2, 4])) {
            $sql = "`#event_id`={$this->firstEventId} AND toDate(`#create_time`, '{$this->timezone}')=toDate({$reg_day}, '{$this->timezone}')";
            // 付费留存(新)
        } elseif ($this->keepType == 3) {
            $pay_event_id = Events::EVENT_PAY;
            $sql =  "
                `#event_id`={$this->firstEventId} AND
                toDate(`#create_time`, '{$this->timezone}')=toDate({$reg_day}, '{$this->timezone}') AND
                `#user_id` IN (
                    SELECT DISTINCT `#user_id` FROM sdk.events_local WHERE `#event_id`={$pay_event_id} AND
                    toDate(`#create_time`, '{$this->timezone}')=toDate({$reg_day}, '{$this->timezone}')
                )
            ";
        // 首充留存
        } elseif ($this->keepType == 5) {
            $pay_event_id   = Events::EVENT_PAY;
            $money_propname = $this->moneyTypeName;
            $money          = $this->money;
            $sql =  "
                `#event_id`={$this->firstEventId} AND
                toDate(`#create_time`, '{$this->timezone}')=toDate({$reg_day}, '{$this->timezone}') AND
                `#user_id` IN (
                    SELECT `#user_id` FROM sdk.events_local
                    WHERE `#event_id`={$pay_event_id}
                    AND toDate(`#create_time`, '{$this->timezone}')=toDate({$reg_day}, '{$this->timezone}')
                    GROUP BY `#user_id`
                    HAVING count(1)=1
                    AND sum({$money_propname})={$money}
                )
            ";
        // 老付费留存
        } elseif ($this->keepType == 6) {
            $reg_event_id = Events::EVENT_REGISTER;
            $sql = "
                `#event_id`={$this->firstEventId} AND
                toDate(`#create_time`, '{$this->timezone}')=toDate({$reg_day}, '{$this->timezone}') AND
                `#user_id` NOT IN (
                    SELECT DISTINCT `#user_id` FROM sdk.events_local
                    WHERE `#event_id`={$reg_event_id}
                    AND toDate(`#create_time`, '{$this->timezone}')=toDate({$reg_day}, '{$this->timezone}')
                )
            ";
        // 老用户留存
        } elseif ($this->keepType == 7) {
            $reg_event_id = Events::EVENT_REGISTER;
            $sql = "
                `#event_id`={$this->firstEventId} AND
                toDate(`#create_time`, '{$this->timezone}')=toDate({$reg_day}, '{$this->timezone}') AND
                `#user_id` NOT IN (
                    SELECT DISTINCT `#user_id` FROM sdk.events_local
                    WHERE `#event_id`={$reg_event_id}
                    AND toDate(`#create_time`, '{$this->timezone}')=toDate({$reg_day}, '{$this->timezone}')
                )
            ";
        } elseif ($this->keepType == 8) {
            $pay_event_id = Events::EVENT_PAY;
            $sql =  "
                `#event_id`={$this->firstEventId} AND
                toDate(`#create_time`, '{$this->timezone}')=toDate({$reg_day}, '{$this->timezone}') AND
                `role_unique_id` IN (
                    SELECT DISTINCT `role_unique_id` FROM sdk.events_local WHERE `#event_id`={$pay_event_id} AND
                    toDate(`#create_time`, '{$this->timezone}')=toDate({$reg_day}, '{$this->timezone}')
                )
            ";
        }

        return $sql;
    }

    protected function getRetention()
    {
        $days      = $this->keepDays;
        $max_dayno = $days[count($days) - 1];
        $stime     = $this->stime;
        $etime     = $this->etime;

        $names      = [];
        $reg_days   = [];
        $retentions = [];
        $selectsums = [];

        // 留存语句
        // yqlog($stime, $etime, date('Ymd', $stime), date('Ymd', $etime));
        for ($reg_day = $stime; $reg_day <= $etime; $reg_day += 86400) {
            $sqls   = [];
            $sqls[] = $this->getRetentionFirstSql($reg_day);
            foreach ($days as $number) {
                $login_day = $reg_day + 86400 * ($number - 1);
                $sqls[]    = "`#event_id`={$this->secondEventId} AND toDate(`#create_time`, '{$this->timezone}')=toDate({$login_day}, '{$this->timezone}')";
            }
            $str          = implode(',', $sqls);
            $name         = "k{$reg_day}";
            $names[]      = $name;
            $reg_days[]   = $reg_day;
            $retentions[] = "retention({$str}) AS {$name}";
        }

        // 汇总select语句
        foreach ($names as $name) {
            $selectsums[] = "sumForEach({$name}) AS {$name}";
        }

        $selectsums = array_chunk($selectsums, $this->maxCutNum);
        foreach ($selectsums as $val) {
            $selectsums_list[] = implode(',', $val);
        }

        $reg_days = array_chunk($reg_days, $this->maxCutNum);
        foreach ($reg_days as $val) {
            $min_reg_time    = $val[0];
            $max_reg_time    = $val[count($val) - 1];
            $max_login_time  = $max_reg_time + $max_dayno * 86400;
            $reg_days_list[] = [$min_reg_time, $max_reg_time, $max_login_time];
        }

        $retentions = array_chunk($retentions, $this->maxCutNum);
        foreach ($retentions as $val) {
            $retentions_list[] = implode(',', $val);
        }

        return [
            'selectsums_list' => $selectsums_list,
            'retentions_list' => $retentions_list,
            'reg_days_list'   => $reg_days_list,
        ];
    }

    protected function queryGroupBy($table_name = 'users')
    {
        $selects = [];
        $groupbys = [];
        foreach ($this->groupbys as $name) {
            switch ($name) {
                case 'stat_hour':
                case 'stat_date':
                case 'stat_week':
                case 'stat_month':
                    break;
                case 'media_id':
                case 'plan_id':
                case 'plan_group_id':
                case 'plan_owner_id':
                    $selects[] = "ad_plan.{$name} AS {$name}";
                    break;
                case 'daili_id':
                    $selects[] = "ad_media_user.{$name} AS {$name}";
                    break;
                case 'adgroup_id':
                    $selects[] = "firstups.adgroup_id AS {$name}";
                    break;
                case 'ad_id':
                    $selects[] = "firstups.ad_id AS {$name}";
                    break;
                case 'creative_id':
                    $selects[] = "firstups.creative_id AS {$name}";
                    break;
                case 'media_user_id':
                    $selects[] = "firstups.{$name} AS {$name}";
                    break;
                default:
                    $selects[] = "users.{$name} AS {$name}";
                    break;
            }
        }

        foreach ($this->groupbys as $name) {
            switch ($name) {
                case 'stat_hour':
                    break;
                case 'stat_date':
                    break;
                case 'stat_week':
                    break;
                case 'stat_month':
                    break;
                default:
                    $groupbys[] = $name;
                    break;
            }
        }

        $select_str  = implode(',', $selects);
        $groupby_str = implode(',', $groupbys);

        return [$select_str, $groupby_str];
    }

    protected function userSelect()
    {
        $selects = [];
        foreach ($this->groupbys as $name) {
            switch ($name) {
                case 'stat_hour':
                case 'stat_date':
                case 'stat_week':
                case 'stat_month':
                case 'media_id':
                case 'plan_id':
                case 'plan_group_id':
                case 'plan_owner_id':
                case 'daili_id':
                case 'media_user_id':
                case 'adgroup_id':
                case 'ad_id':
                case 'creative_id':
                    break;
                default:
                    $selects[] = "{$name}";
                    break;
            }
        }

        $select_str = implode(',', $selects);

        return $select_str;
    }

    public function getSqlByGroup()
    {
        $ret = $this->getRetention();
        if (!$ret) {
            return false;
        }

        $selectsums_list = $ret['selectsums_list'];
        $retentions_list = $ret['retentions_list'];
        $reg_days_list   = $ret['reg_days_list'];

        $join             = $this->queryJoin('users');
        $user_where       = $this->queryWhere(['game_id', 'channel', 'game_channel_id', 'create_time', 'device_type'], 'users');
        $plan_where       = $this->queryWhere(['media_id', 'media_docking_id', 'owner', 'plan_group_id', 'plan_id', 'source_type'], 'ad_plan');
        $media_user_where = $this->queryWhere(['daili', 'media_user_id'], 'ad_media_user');


        list($select_str, $groupby_str) = $this->queryGroupBy();
        $user_select_str = $this->userSelect();
        if ($user_select_str != '') {
            $user_select_str = ", {$user_select_str}";
        }

        // 如果大于1个月此语句性能更好
        $sqls = [];
        foreach ($selectsums_list as $id => $selectsums_str) {
            $retentions_str = $retentions_list[$id];
            $min_reg_time   = $reg_days_list[$id][0];
            $max_login_time = $reg_days_list[$id][2];

            if ($select_str == '') {
                $sub_select_str = "events.`#user_id`";
            } else {
                $sub_select_str = "events.`#user_id`, {$select_str}";
            }

            $total_groupby_str = '';
            if ($groupby_str == '') {
                $groupby_select = "{$selectsums_str}";
                $sub_groupby_str = "`#user_id`";
            } else {
                $groupby_select = "{$groupby_str}, {$selectsums_str}";
                $sub_groupby_str = "`#user_id`, {$groupby_str}";
                $total_groupby_str = "GROUP BY {$groupby_str}";
            }

            $sql = "
                SELECT
                {$groupby_select}
                FROM(
                    SELECT
                    {$sub_select_str}, {$retentions_str}
                    FROM sdk.events
                    JOIN(
                        SELECT `#user_id`, `#create_time`, firstups.*, ad_plan.*, ad_media_user.* {$user_select_str} FROM sdk.users_local AS users
                        {$join}
                        WHERE {$user_where}
                        AND {$plan_where}
                        AND {$media_user_where}
                    ) AS users
                    ON users.`#user_id` = events.`#user_id`
                    WHERE events.`#event_id` IN({$this->firstEventId}, {$this->secondEventId})
                    AND events.`#create_time` >= {$min_reg_time}
                    AND events.`#create_time` < {$max_login_time}
                    GROUP BY {$sub_groupby_str}
                )
                {$total_groupby_str}
            ";

            $sqls[] = $sql;
        }

        return $sqls;
    }
}
